package gestordeentidades.persistance.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import gestordeentidades.persistance.interfaces.IDataBaseAdapter;
import gestordeentidades.persistance.interfaces.IDataBaseRow;
import gestordeentidades.persistance.interfaces.IDataBaseStatement;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;

/**
 * Trabalho DAS - 2014/2015 Classe que implementa o IDataBaseAdapter para a base
 * de dados Sqlite
 *
 * @author a21210380, a21190325, a21210392
 */
public class DataBase implements IDataBaseAdapter {

    static {
        // fazer load do JDBC para sqlite
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex);
        }
    }

    private static final Logger logger = Logger.getLogger(DataBase.class.getName());
    public final static int CON_TIMEOUT = 30;
    public final static String DATA_BASE_NAME = "das.db";

    @Override
    public void ler(IDataBaseStatement dataBaseStatement, IDataBaseRow dataBaseRow) {
        if (dataBaseStatement == null) {
            throw new NullPointerException("O dataBaseStatement não pode ser null!");
        }
        if (dataBaseRow == null) {
            throw new NullPointerException("O dataBaseRow não pode ser null!");
        }
        
        PreparedStatement preparedStatement = null;
        //obter um PreparedStatement com o sql do dataBaseStatement
        try {
            preparedStatement = getPreparedStatement(dataBaseStatement.getSql());
            // preencher o PreparedStatement com os paramentros no dataBaseStatement
            for (ParametroSql c : dataBaseStatement) {
                insertArgument(c, preparedStatement);
            }

            // obter a informacao da metadata do preparedStatement
            // contém nome das colunas etc
            ResultSetMetaData metaData = preparedStatement.getMetaData();

            // criar uma linha com o tamanho inicial igual ao numero de colunas
            Linha linha = new Linha(metaData.getColumnCount());

            // executar o preparedStatement, se obtém valaores return true
            if (preparedStatement.execute()) {
                ResultSet res = null;
                // obter um ResultSet
                try {
                    res = preparedStatement.getResultSet();
                    // se existirem valores o ResultSet != null
                    if (res != null) {
                        // ler linha a linha
                        while (res.next()) {
                            linha.clear();
                            for (int i = 1; i <= metaData.getColumnCount(); ++i) {
                                linha.put(metaData.getColumnName(i), res.getObject(i));
                            }
                            // indicar que foi lida uma linha ao dataBaseRow
                            dataBaseRow.onLinhaLida(linha);
                        }
                    }
                } catch (SQLException ex) {
                    logger.logp(Level.SEVERE, DataBase.class.getName(), "getResultSet()", dataBaseStatement.getSql(), ex);
                } finally {
                    if(res != null){
                        try {
                            res.close();
                        } catch (SQLException ex) {}
                    }
                }
            }
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "executeRead()", dataBaseStatement.getSql(), ex);
        } finally {
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                    preparedStatement.getConnection().close();
                } catch (SQLException ex) {}
            }
        }
    }

    @Override
    public int atualizar(IDataBaseStatement dataBaseStatement) {
        if (dataBaseStatement == null) {
            throw new NullPointerException("O dataBaseStatement não pode ser null!");
        }
        
        PreparedStatement preparedStatement = null;
        //obter um PreparedStatement com o sql do dataBaseStatement
        try {
            preparedStatement = getPreparedStatement(dataBaseStatement.getSql());
            // preencher o PreparedStatement com os paramentros no dataBaseStatement
            for (ParametroSql c : dataBaseStatement) {
                insertArgument(c, preparedStatement);
            }
            // executar e devolver o resultado
            return preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "executeUpdate()", dataBaseStatement.getSql(), ex);
        } finally {
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                    preparedStatement.getConnection().close();
                } catch (SQLException ex) {}
            }
        }
        
        return 0;
    }

    @Override
    public int inserir(IDataBaseStatement dataBaseStatement) {
        if (dataBaseStatement == null) {
            throw new NullPointerException("O dataBaseStatement não pode ser null!");
        }
        
        PreparedStatement preparedStatement = null;
        //obter um PreparedStatement com o sql do dataBaseStatement
        try {
            preparedStatement = getPreparedStatement(dataBaseStatement.getSql());
            // preencher o PreparedStatement com os paramentros no dataBaseStatement
            for (ParametroSql c : dataBaseStatement) {
                insertArgument(c, preparedStatement);
            }
            // executar e devolver o resultado
            return preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "executeInsert()", dataBaseStatement.getSql(), ex);
        } finally {
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                    preparedStatement.getConnection().close();
                } catch (SQLException ex) {}
            }
        }
        return 0;
    }
    
    @Override
    public int apagar(IDataBaseStatement dataBaseStatement) {
        if (dataBaseStatement == null) {
            throw new NullPointerException("O dataBaseStatement não pode ser null!");
        }
        
        PreparedStatement preparedStatement = null;
        //obter um PreparedStatement com o sql do dataBaseStatement
        try {
            preparedStatement = getPreparedStatement(dataBaseStatement.getSql());
            // preencher o PreparedStatement com os paramentros no dataBaseStatement
            for (ParametroSql c : dataBaseStatement) {
                insertArgument(c, preparedStatement);
            }
            // executar e devolver o resultado
            return preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "apagar()", dataBaseStatement.getSql(), ex);
        } finally {
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                    preparedStatement.getConnection().close();
                } catch (SQLException ex) {}
            }
        }
        return 0;
    }

    @Override
    public void executarSql(String sql) {
        if (sql == null) {
            throw new NullPointerException("O dataBaseStatement não pode ser null!");
        }
        //obter um Statement
        Statement preparedStatement = null;
        try {
            preparedStatement = getStatement();
            // executar o Statement com o sql recebido
            preparedStatement.execute(sql);
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "executeSql()", sql, ex);
        } finally {
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                    preparedStatement.getConnection().close();
                } catch (SQLException ex) {}
            }
        }
    }

    @Override
    public IDataBaseStatement getDataBaseStatement(String sql) {
        return new DataBaseStatement().setSql(sql);
    }
    
    public static boolean apagarBaseDeDados() {
        try {
            File f = new File(DATA_BASE_NAME);
            return Files.deleteIfExists(f.toPath());
        } catch (SecurityException | IOException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "apagarBaseDeDados()", "permissões de escrita talvez", ex);
            return false;
        }
    }

    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:sqlite:" + DATA_BASE_NAME);
    }

    private static PreparedStatement getPreparedStatement(String sql) {
        try {
            Connection connection = getConnection();
            return connection.prepareStatement(sql);
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "getPreparedStatement()", sql, ex);
            return null;
        }
    }

    private static Statement getStatement() {
        try {
            Connection connection = getConnection();
            return connection.createStatement();
        } catch (SQLException ex) {
            logger.logp(Level.SEVERE, DataBase.class.getName(), "getStatement()", "", ex);
            return null;
        }
    }

    private static void insertArgument(ParametroSql c, PreparedStatement preparedStatement) throws SQLException {
        switch (c.getTargetSqlType()) {
            case INTEGER:
                preparedStatement.setInt(c.getIndex(), (int) c.getObjecto());
                break;
            case BIGINT:
                preparedStatement.setLong(c.getIndex(), (long) c.getObjecto());
                break;
            case FLOAT:
                preparedStatement.setFloat(c.getIndex(), (float) c.getObjecto());
                break;
            case DOUBLE:
                preparedStatement.setDouble(c.getIndex(), (double) c.getObjecto());
                break;
            case BIT:
                preparedStatement.setBoolean(c.getIndex(), (boolean) c.getObjecto());
                break;
            case TEXT:
                preparedStatement.setString(c.getIndex(), (String) c.getObjecto());
                break;
            case DATE:
                preparedStatement.setDate(c.getIndex(), (Date) c.getObjecto());
                break;
        }
    }
}
